package com.weixinadmin.dao;
import java.sql.Connection;   
import java.sql.PreparedStatement;   
import java.sql.ResultSet;   
import java.sql.SQLException;   
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;   
  






import javax.sql.DataSource;   

import org.apache.log4j.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;   
import org.springframework.jdbc.core.PreparedStatementCreator;   
import org.springframework.jdbc.core.RowMapper;   
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;   
import org.springframework.jdbc.support.KeyHolder;   

import com.weixindamin.exception.wxexception;

public class CustomerGroupSql {
	  private static Logger logger = Logger.getLogger(CustomerGroupSql.class);//log4j
	
	  private JdbcTemplate jdbcTemplate;   
      //private NamedParameterJdbcTemplate namedParameterJdbcTemplate;   
	  //private SimpleJdbcTemplate simpleJdbcTemplate;   
	  private CustomerGroupdao cg;
	  private final static String table = "customergroup" ;
	  
	
	  public CustomerGroupSql(){
			 this.jdbcTemplate = new JdbcManager().getjdbctemplate();
		 } 	 
	  
	class custmergroupRowMapper implements RowMapper {  
		        
		public Object mapRow(ResultSet rs, int index) throws SQLException {  
		CustomerGroupdao cg = new CustomerGroupdao(rs.getInt("id"),rs.getInt("count"),rs.getString("name"));  		      
		//cg.setid(rs.getInt("id"));      		      
		//cg.setname(rs.getString("name"));    		      
		//cg.setcount(rs.getInt("count"));      
		return cg;         	      
		}         
		      
	 }  
	 
	  
	  public List<CustomerGroupdao> getcustmergroup() {

		  // TODO Auto-generated method stub
		  if(null==jdbcTemplate){
			  logger.error("jdbcTemplate is null");
		  }
          return jdbcTemplate .query( "select * from "+table ,
        		  new custmergroupRowMapper());

	  } 
	  
	  public CustomerGroupdao getcustmergroup(int id) {

          // TODO Auto-generated method stub
		  if(null==jdbcTemplate){
			  logger.error("jdbcTemplate is null");
		  }
          return (CustomerGroupdao) jdbcTemplate.queryForObject(
        		  "select * from "+table+" where id=?" , new Object[] { id }, 
                   new int [] { java.sql.Types. INTEGER }, new custmergroupRowMapper());

	  }
	  
	  public void add(CustomerGroupdao cg) throws wxexception{
		  
		  /*
		  	if(null==jdbcTemplate){
				  logger.info("jdbcTemplate is null");
				  return ;
			}
			*/
		  	String sql = "INSERT INTO "+table+" (id,name,count) VALUES(?,?,?)";
			try { 
				  jdbcTemplate.update(sql,
						  new Object[]{cg.getid(),cg.getname(),cg.getcount()});
			} catch (Exception e) {  				 
				  logger.error("sql Exception: "+ sql);
				  e.printStackTrace();   
				  throw new wxexception(wxexception.sqlerr,sql);
			}  
	  }
	    
	  public void del() throws wxexception{	
		//  return jdbcTemplate.update("delete * from "+table, new Object[]{});
		  /*
		  if(null==jdbcTemplate){
			  logger.info("jdbcTemplate is null");
			  return ;
		  }
		  */
		  String sql = "delete * from "+table;
		  try { 
			  jdbcTemplate.update(sql,
					  new Object[]{});
		  }	catch (Exception e) {  
			  logger.error("sql Exception: "+ sql);
			  e.printStackTrace();   
			  throw new wxexception(wxexception.sqlerr,sql);
		  } 
	  
	  }
	 	
	  
	  public static void main(String[] args)  {
		  
		  ApplicationContext ctx = new ClassPathXmlApplicationContext("springjdbc.xml");
		  DataSource dataSource = (DataSource)ctx.getBean("dataSource");
		  CustomerGroupSql cgs = new CustomerGroupSql();
		//  cgs.setDataSource(dataSource);
		  CustomerGroupdao cg = new CustomerGroupdao(2,3,"gg");
		 // cgs.add(cg);
		  cg = cgs.getcustmergroup(1);
		  System.out.println("Hello World!" + cg.getname()+cg.getid()+cg.getcount());
		  List<CustomerGroupdao> cgss =  cgs.getcustmergroup();
		  
		  Iterator<CustomerGroupdao> itr = cgss.iterator();
	        while (itr.hasNext()) {
	        	cg = itr.next();
	        	System.out.println("Hello World!" + cg.getname()+cg.getid()+cg.getcount());
	    		 
	        }
         
  } 
}



